package br.com.fiap.mercadinho.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.fiap.mercadinho.bean.Order;
import br.com.fiap.mercadinho.common.DAOException;
import br.com.fiap.mercadinho.common.GenericDAOException;

public class OrderDAO extends GenericDAO {

	private static final String FIND_ORDER_BY_CUSTOMER = " SELECT ORDER_ID,CUSTOMER_ID FROM ORDERS WHERE CUSTOMER_ID = ? ";
	
	private static final String FIND_ORDER_BY_ID = " SELECT ORDER_ID,CUSTOMER_ID FROM ORDERS WHERE ORDER_ID = ?";
	
	private static final String INSERT_ORDER = " INSERT INTO ORDERS (ORDER_ID,CUSTOMER_ID) VALUES (0,?) ";
	
	private final static String LAST_PRODUCT_ID = "SELECT LAST_INSERT_ID() LAST_ID ";
	
	public void saveOrder(Order order) throws DAOException{
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(INSERT_ORDER);
			pst.setInt(1,order.getCustomerId());
			pst.executeUpdate();
			//recovery the last id save in the table of products
			pst = conn.prepareStatement(LAST_PRODUCT_ID);
			ResultSet rs = pst.executeQuery();
			if (rs.next())
				this.lastInsertId = rs.getInt("LAST_ID");
			
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in insert product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}
	
	public Order findOrderById(Integer orderId) throws DAOException{
		try {

			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_ORDER_BY_ID);
			pst.setInt(1, orderId);
			ResultSet rs = pst.executeQuery();

			if (rs.next())
				return buildOrder(rs);

		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in find product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
		return null;
	}
	
	public List<Order> findOrderByCustomerId(Integer id) throws DAOException{
		
		try {

			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_ORDER_BY_CUSTOMER);
			pst.setInt(1, id);
			ResultSet rs = pst.executeQuery();
			List<Order> orders = new ArrayList<Order>();	
			while (rs.next())
				orders.add(buildOrder(rs));

			return orders;
			
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in find product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}
	
	private Order buildOrder(ResultSet rs) throws DAOException {
		Order order = new Order();
		CustomerDAO customerDAO = new CustomerDAO();
		ProductOrderDAO productOrderDAO = new ProductOrderDAO();
		try {
			order.setId(rs.getInt("ORDER_ID"));
			order.setCustomer(customerDAO.findCustomerById(rs.getInt("CUSTOMER_ID")));
			order.setProducts(productOrderDAO.findProductsByOrderId(rs.getInt("ORDER_ID")));
			return order;
		} catch (SQLException e) {
			throw new DAOException("Error in build product method: "
					+ e.getMessage());
		}

	}
	
}
